package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import com.dudu.model.SmsInfo;

import core.CoreDao;
import core.db.DBConnectionUtil;

public class SmsInfoDao {
	
	public int insert(CoreDao dao, SmsInfo bean) {
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_sms_info(app_key, cust_account, user_identifer, signcode," +
					"signature, template_code, msg_id, content, phones," +
					"session_id, phones_amount, status, status_desc, audit_status," +
					"ip, create_time, comments,ext_number)");
			sql.append("VALUES(?,?,?,?," +
					"?,?,?,?,?," +
					"?,?,?,?,?," +
					"?,sysdate(),?,?)");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						bean.getApp_key(),  bean.getCust_account(), bean.getUser_identifer(), bean.getSigncode(),
						bean.getSignature(), bean.getTemplate_code(), bean.getMsg_id(), bean.getContent(), bean.getPhones(),
						bean.getSession_id(), bean.getPhones_amount(), bean.getStatus(), bean.getStatus_desc(), bean.getAudit_status(),
						bean.getIp(), bean.getComments(),bean.getExt_number()
							});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 监测短信验证码的
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return totalAmount errorAmount delayAmount noStatusAmount
	 */
	public JSONObject queryWatchingAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		JSONObject result = null;
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append(" 	( ");
			sql.append(" 		SELECT ");
			sql.append(" 			count(DISTINCT(i.id)) ");
			sql.append(" 		FROM ");
			sql.append(" 			t_sms_info i ");
			sql.append(" 		WHERE ");
			sql.append(" 			i.create_time >= ? ");
			sql.append(" 		AND i.create_time < ? ");
			sql.append(" 		AND i.content LIKE '%验证码%' ");
			sql.append(" 	) AS 'totalAmount', ");
			sql.append(" 	( ");
			sql.append(" 		SELECT ");
			sql.append(" 			count(DISTINCT(i.id)) ");
			sql.append(" 		FROM ");
			sql.append(" 			t_sms_info i ");
			sql.append(" 		WHERE ");
			sql.append(" 			i.create_time >= ? ");
			sql.append(" 		AND i.create_time < ? ");
			sql.append(" 		AND i.content LIKE '%验证码%' ");
			sql.append(" 		AND i.`status` = '-1' ");
			sql.append(" 	) AS 'errorAmount', ");
			sql.append(" 	( ");
			sql.append(" 		SELECT ");
			sql.append(" 			count(DISTINCT(i.id)) ");
			sql.append(" 		FROM ");
			sql.append(" 			t_sms_info i, ");
			sql.append(" 			t_sms_info_detail d ");
			sql.append(" 		WHERE ");
			sql.append(" 			i.session_id = d.session_id ");
			sql.append(" 		AND d.create_time >= ? ");
			sql.append(" 		AND d.create_time < ? ");
			sql.append(" 		AND i.content LIKE '%验证码%' ");
			sql.append(" 		AND TIMESTAMPDIFF( ");
			sql.append(" 			SECOND, ");
			sql.append(" 			i.create_time, ");
			sql.append(" 			d.send_time ");
			sql.append(" 		) > 20 ");
			sql.append(" 	) AS 'delayAmount', ");			
			sql.append("	( ");
			sql.append("		SELECT ");
			sql.append("			count(DISTINCT(i.id)) ");
			sql.append("		FROM ");
			sql.append("			t_sms_info i ");
			sql.append("		WHERE ");
			sql.append("			i.create_time >= ? ");
			sql.append("		AND i.create_time < ? ");
			sql.append("		AND i.content LIKE '%验证码%' ");
			sql.append("		AND i.`status` = '0' ");
			sql.append("		AND ( ");
			sql.append("			SELECT ");
			sql.append("				COUNT(1) ");
			sql.append("			FROM ");
			sql.append("				t_sms_info_detail d ");
			sql.append("			WHERE ");
			sql.append("				d.session_id = i.session_id ");
			sql.append("		) = 0 ");
			sql.append("	) AS 'noStatusAmount' ");
			sql.append(" FROM ");
			sql.append(" 	DUAL" );
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime,  
						beginTime, endTime,
						beginTime, endTime,
						beginTime, endTime
					});
			if(list != null && list.size()>0){
				result = list.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
	
	/**
	 * 监测短信验证码的下发失败
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return errorAmount
	 */
	public JSONObject queryErrorAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		JSONObject result = null;
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT	 ");
			sql.append(" 	count(DISTINCT(i.id)) AS 'errorAmount' ");
			sql.append(" FROM	 ");
			sql.append("  	t_sms_info i,   ");
			sql.append("  	t_sms_info_detail d	 ");
			sql.append(" WHERE    ");
			sql.append("  	i.session_id = d.session_id	 ");
			sql.append("    AND i.create_time >=? ");
			sql.append(" 	AND i.create_time <? ");
			sql.append("    AND i.content LIKE '%验证码%' ");
			sql.append("    AND d.`status` <> 0 ");
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime
					});
			if(list != null && list.size()>0){
				result = list.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
	
	/**
	 * 监测短信验证码的下发延时
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return delayAmount noStatusAmount
	 */
	public JSONObject queryDelayAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		JSONObject result = null;
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append(" 	( ");
			sql.append(" 		SELECT ");
			sql.append(" 			count(DISTINCT(i.id)) ");
			sql.append(" 		FROM ");
			sql.append(" 			t_sms_info i, ");
			sql.append(" 			t_sms_info_detail d ");
			sql.append(" 		WHERE ");
			sql.append(" 			i.session_id = d.session_id ");
			sql.append(" 		AND i.create_time >= ? ");
			sql.append(" 		AND i.create_time < ? ");
			sql.append(" 		AND i.content LIKE '%验证码%' ");
			sql.append(" 		AND TIMESTAMPDIFF( ");
			sql.append(" 			SECOND, ");
			sql.append(" 			i.create_time, ");
			sql.append(" 			d.send_time ");
			sql.append(" 		) > 20 ");
			sql.append(" 	) AS 'delayAmount', ");			
			sql.append("	( ");
			sql.append("		SELECT ");
			sql.append("			count(DISTINCT(i.id)) ");
			sql.append("		FROM ");
			sql.append("			t_sms_info i ");
			sql.append("		WHERE ");
			sql.append("			i.create_time >= ? ");
			sql.append("		AND i.create_time < ? ");
			sql.append("		AND i.content LIKE '%验证码%' ");
			sql.append("		AND i.`status` = '0' ");
			sql.append("		AND ( ");
			sql.append("			SELECT ");
			sql.append("				COUNT(1) ");
			sql.append("			FROM ");
			sql.append("				t_sms_info_detail d ");
			sql.append("			WHERE ");
			sql.append("				d.session_id = i.session_id ");
			sql.append("		) = 0 ");
			sql.append("	) AS 'noStatusAmount' ");
			sql.append(" FROM ");
			sql.append(" 	DUAL" );
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime,  
						beginTime, endTime
					});
			if(list != null && list.size()>0){
				result = list.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
	
	/**
	 * 查询时间端验证码短信下发失败率
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return
	 */
	public JSONObject queryErrorPercent(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		JSONObject result = null;
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append(" FORMAT( ");
			sql.append("		( ");
			sql.append("			SELECT ");
			sql.append("				count(DISTINCT(i.id)) AS 'errorAmount' ");
			sql.append("			FROM ");
			sql.append("				t_sms_info i, ");
			sql.append("				t_sms_info_detail d ");
			sql.append("			WHERE ");
			sql.append("				i.session_id = d.session_id ");
			sql.append("			AND i.create_time >= ? ");
			sql.append("			AND i.create_time < ? ");
			sql.append("			AND i.content LIKE '%验证码%' ");
			sql.append("			AND d.`status` <> 0 ");
			sql.append("		) / ( ");
			sql.append("			SELECT ");
			sql.append("				count(i.id) AS 'totalAmount' ");
			sql.append("			FROM ");
			sql.append("				t_sms_info i ");
			sql.append("			WHERE ");
			sql.append("				i.create_time >= ? ");
			sql.append("			AND i.create_time < ? ");
			sql.append("			AND i.content LIKE '%验证码%' ");
			sql.append("		), ");
			sql.append("		2 ");
			sql.append("	) AS per ");
			sql.append(" FROM ");
			sql.append("	DUAL ");
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime,  
						beginTime, endTime
					});
			if(list != null && list.size()>0){
				result = list.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
	
}
